package sg.edu.nus.iss.phoenix.schedule.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.iss.phoenix.dao.DBConnection;
import sg.edu.nus.iss.phoenix.schedule.dao.AnnualScheduleDAO;
import sg.edu.nus.iss.phoenix.schedule.entity.AnnualSchedule;

/**
 * @author Wang Shunde
 *
 */
public class AnnualScheduleDAOImpl implements AnnualScheduleDAO {

	private Connection connection;
	private static final String SEARCH_ANNUAL_SCHEDULE="SELECT * FROM annual_schedule_mst where schedule_year=?";
	private static final String SEARCH_ALL_ANNUAL_SCHEDULE = "SELECT * FROM annual_schedule_mst";
	private static final String CREATE_ANNUAL_SCHEDULE = "INSERT into annual_schedule_mst VALUES(?,?,?,?,?)";
	private static final String DELETE_ANNUAL_SCHEDULE = "DELETE FROM annual_schedule_mst where schedule_year=?";	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.AnnualScheduleDAO#searchMatching(int)
	 */
	@Override
	public AnnualSchedule searchMatching(int schedule_year) throws SQLException {
		openConnection();
		AnnualSchedule as = new AnnualSchedule();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(SEARCH_ANNUAL_SCHEDULE);
			ps.setInt(1, schedule_year);
			rs = ps.executeQuery();
			while(rs.next()) {
				as.setYear(rs.getInt("schedule_year"));
			}
		}
		finally {
			if(ps !=null)
				ps.close();
			if(rs != null)
				rs.close();
			closeConnection();
		}
		return as;
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.AnnualScheduleDAO#searchMatching()
	 */
	@Override
	public List<AnnualSchedule> searchMatching() throws SQLException {
		openConnection();
		List<AnnualSchedule> annualSchedules = new ArrayList<AnnualSchedule>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(SEARCH_ALL_ANNUAL_SCHEDULE);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				AnnualSchedule as = new AnnualSchedule();
				as.setYear(rs.getInt("schedule_year"));
				annualSchedules.add(as);
			}
			return annualSchedules;
		}
		finally {
			if(ps !=null)
				ps.close();
			if(rs != null)
				rs.close();
			closeConnection();
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.AnnualScheduleDAO#createAnnualSchedule(int, long)
	 */
	@Override
	public void createAnnualSchedule(int year, long userId) throws SQLException {
		openConnection();
		PreparedStatement ps = null;
		try {
			Date now = new java.sql.Date(Utility.now.getTime());
			ps = connection.prepareStatement(CREATE_ANNUAL_SCHEDULE);
			ps.setInt(1, year);
			ps.setLong(2, userId);
			ps.setDate(3, now);
			ps.setLong(4, userId);
			ps.setDate(5, now);
			ps.execute();
		}
		finally {
			if(ps !=null)
				ps.close();
			closeConnection();
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see sg.edu.nus.iss.phoenix.schedule.dao.AnnualScheduleDAO#deleteAnnualSchedule(int)
	 */
	@Override
	public void deleteAnnualSchedule(int year) throws SQLException {
		openConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(DELETE_ANNUAL_SCHEDULE);
			ps.setInt(1, year);
			ps.execute();
		}
		finally {
			if(ps !=null)
				ps.close();
			closeConnection();
		}
	}
	
	/**
	 * Utility method to open connection to database.
	 */
	private void openConnection() {
		try {
			DBConnection dbConnection = new DBConnection();
			this.connection = dbConnection.getDBConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	/**
	 * Utility method to close database connection
	 */
	private void closeConnection() {
		try {
			DBConnection dbConnection = new DBConnection();
			dbConnection.closeConnection(this.connection);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
